﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using XQ.WeChatOfficialAccount.DAL.Model;

namespace XQ.WeChatOfficialAccount.DAL.DAL
{
    public class DictDal:BaseDal<Sys_Dics>
    {
        /// <summary>
        /// 获取指定类型字典值列表
        /// </summary>
        /// <returns></returns>
        public List<DictModel> GetDictList(int categoryId)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                string sql = "select d.*,dc.id as DictCategoryId,dc.Title as DictCategoryTitle from Sys_Dics d inner join Sys_DicCategory dc on d.CategoryId=dc.ID where (d.isdel is null or d.isdel=0) and parentID is null and dc.id=@dcID order by d.sortnum";
                object[] par = { 
                            new SqlParameter("@dcID",categoryId),
                            };
                return ce.Database.SqlQuery<DictModel>(sql, par).ToList();
            }
        }

        /// <summary>
        /// 获取字典ID获取字典子项列表
        /// </summary>
        /// <returns></returns>
        public List<DictModel> GetChildrens(int dictID)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                string sql = "select * from Sys_Dics d where (d.isdel is null or d.isdel=0) and d.parentID=@dictID order by d.sortnum";
                object[] par = {
                            new SqlParameter("@dictID",dictID),
                            };
                return ce.Database.SqlQuery<DictModel>(sql, par).ToList();
            }
        }

        /// <summary>
        /// 根据字典分类Code获取当前分类的字典列表
        /// </summary>
        /// <param name="categoryCode"></param>
        /// <returns></returns>
        public List<DictModel> GetDictByCode(string categoryCode)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                string sql = "select d.*,dc.id as DictCategoryId,dc.Title as DictCategoryTitle from Sys_Dics d inner join Sys_DicCategory dc on d.CategoryId=dc.ID where (d.isdel is null or d.isdel=0) and dc.code=@categoryCode order by d.sortnum";
                object[] par = {
                            new SqlParameter("@categoryCode",categoryCode),
                            };
                return ce.Database.SqlQuery<DictModel>(sql, par).ToList();
            }
        }

        /// <summary>
        /// 根据字典分类Code获取当前分类的字典列表
        /// </summary>
        /// <param name="categoryCode"></param>
        /// <returns></returns>
        public List<DictModel> GetALLDictByCode(string categoryCode)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                string sql = "select d.*,dc.id as DictCategoryId,dc.Title as DictCategoryTitle from Sys_Dics d inner join Sys_DicCategory dc on d.CategoryId=dc.ID where (d.isdel is null or d.isdel=0) and dc.code like @categoryCode+'%' order by d.sortnum";
                object[] par = {
                            new SqlParameter("@categoryCode",categoryCode),
                            };
                return ce.Database.SqlQuery<DictModel>(sql, par).ToList();
            }
        }

        public Sys_Dics GetDictById(int dictId)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                return ce.Sys_Dics.FirstOrDefault(dc => dc.ID == dictId);
            }
        }

        /// <summary>
        /// 根据字典ID获取字典名称
        /// </summary>
        /// <param name="dictId"></param>
        /// <returns></returns>
        public string GetDictTitleById(int dictId)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                Sys_Dics dict = ce.Sys_Dics.FirstOrDefault(dc => dc.ID == dictId);
                return dict == null ? "" : dict.Title;
            }
        }

        /// <summary>
        /// 根据编号获取字典名称
        /// </summary>
        /// <param name="dictId"></param>
        /// <returns></returns>
        public Sys_Dics GetDictNameByCode(string code)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                return ce.Sys_Dics.FirstOrDefault(dc => dc.Code == code);
            }
        }

        /// <summary>
        /// 新增字典项
        /// </summary>
        /// <param name="dic"></param>
        /// <returns></returns>
        public bool SaveDict(Sys_Dics dic)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                ce.Sys_Dics.Add(dic);
                if (ce.SaveChanges() == 1)
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// 修改字典项
        /// </summary>
        /// <param name="dic"></param>
        /// <returns></returns>
        public bool UpdateDict(Sys_Dics dic)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                Sys_Dics oldDict = ce.Sys_Dics.First(d => d.ID == dic.ID);
                oldDict.Title = dic.Title;
                oldDict.Code = dic.Code;
                oldDict.ParentId = dic.ParentId;
                oldDict.Sortnum = dic.Sortnum;
                oldDict.Remark = dic.Remark;
                if (ce.SaveChanges() == 1)
                {
                    return true;
                }
            }
            return false;
        }


        /// <summary>
        /// 删除字典类型（标识删除）
        /// </summary>
        /// <param name="dictId"></param>
        /// <returns></returns>
        public bool DeleteDict(int dictId)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                //标识删除
                Sys_Dics oldDic = ce.Sys_Dics.First(dc => dc.ID == dictId);
                oldDic.IsDel = true;
                if (ce.SaveChanges() == 1)
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// 批量删除字典
        /// </summary>
        public bool DelDicts(List<int> dictIds)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                if (dictIds != null && dictIds.Count > 0)
                {
                    foreach (int dictId in dictIds)
                    {
                        Sys_Dics oldDic = ce.Sys_Dics.FirstOrDefault(dc => dc.ID == dictId);
                        oldDic.IsDel = true;
                    }
                    return ce.SaveChanges() > 0;
                }
            }
            return false;
        }

        /// <summary>
        /// 获取字典ID按title
        /// </summary>
        /// <param name="title"></param>
        /// <returns></returns>
        public int GetDicIDByTitle(string title)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                var firstOrDefault = ce.Sys_Dics.FirstOrDefault(n => n.Title == title);
                if (firstOrDefault != null)
                    return firstOrDefault.ID;

                else
                {
                    return 0;
                }
            }
        }

        /// <summary>
        /// 根据区系统title获取本系统字典值ID（案件分类）
        /// </summary>
        /// <param name="title"></param>
        /// <returns></returns>
        public int GetDictIDByTitle(string title)
        {
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {//CategoryId == 9 案件分类
                var firstOrDefault = ce.Sys_Dics.FirstOrDefault(n => n.Title == title && n.CategoryId == 9);
                if (firstOrDefault != null)
                    return firstOrDefault.ID;

                else
                {
                    return 0;
                }
            }
        }
        /// <summary>
        /// 根据CategoryId获取字典名称
        /// </summary>
        /// <param name="CategoryId"></param>
        /// <returns></returns>
        public List<Sys_Dics> GetDictValue(int? CategoryId)
        {
            List<Sys_Dics> list = new List<Sys_Dics>();
            using (WeChatOfficialAccountEntities ce = new WeChatOfficialAccountEntities())
            {
                var match = from u in db.Sys_Dics
                            where u.CategoryId == CategoryId && u.IsDel == null
                            orderby u.Sortnum
                            select u;
                
                var user = match.FirstOrDefault();
                list = match.ToList();
            }
            return list;
        }

        /// <summary>
        /// 根据字符串字典ID获取字典列表
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public List<Sys_Dics> GetDicsByIds(string ids)
        {
            using (WeChatOfficialAccountEntities he = new WeChatOfficialAccountEntities())
            {
                return he.Database.SqlQuery<Sys_Dics>("select * from Sys_Dics where id in(" + ids + ")").ToList();
            }
        }
    }
}
